﻿CREATE PROCEDURE [admin].[s_Borrower_Fix] AS

WHILE 1 = 1 BEGIN

UPDATE L_B SET Pos = Pos-1
FROM t_Loan_Borrower L_B INNER JOIN
(SELECT LoanID FROM t_Loan_Borrower
 GROUP BY LoanID HAVING MIN(Pos) > 0
)P ON L_B.LoanID = P.LoanID
IF @@ROWCOUNT = 0  BREAK

END

DECLARE @SQL varchar(max)
SET @SQL = (
SELECT dbo.sprint3('EXEC s_Borrower_Add %s,%s%s',T.ID,0,CHAR(10))
FROM
(
SELECT     L.ID, L.DealerSalesPersonID, L.DealerMsID, L.MsID, L.Ms_CLASS, L.Ms_ACCOUNT, L.EnterDate, L.TitleOk, L.LienHolderOnTitle, L.TitleHTML, 
                      L.NameSpace, L.NameSpaceID, L.BreakdownCode, L.StatusID, L.StatusDate, L.StatusUser, L.ContractHolderID, L.DateCreated, L.ApplicantID, 
                      L.CompanyID, L.GUID_Documents
FROM         t_Loan AS L LEFT OUTER JOIN
                      t_CarInfo AS CI ON L.ID = CI.LoanID LEFT OUTER JOIN
                      t_Loan_Borrower AS L_B ON L.ID = L_B.LoanID
WHERE     (L_B.LoanID IS NULL)
)T  WHERE T.ID >= 1050966 FOR XML PATH('')
)
PRINT @SQL
EXEC (@SQL)

--BEGIN TRAN
--EXECUTE AS Login='NT AUTHORITY\SYSTEM'
--
--SELECT LoanID FROM t_Loan_Borrower
-- GROUP BY LoanID HAVING MIN(Pos) > 0
--
--EXEC admin.s_Borrower_Fix
--
--SELECT LoanID FROM t_Loan_Borrower
-- GROUP BY LoanID HAVING MIN(Pos) > 0
--SELECT     L.ID, L.DealerSalesPersonID, L.DealerMsID, L.MsID, L.Ms_CLASS, L.Ms_ACCOUNT, L.EnterDate, L.TitleOk, L.LienHolderOnTitle, L.TitleHTML, 
--                      L.NameSpace, L.NameSpaceID, L.BreakdownCode, L.StatusID, L.StatusDate, L.StatusUser, L.ContractHolderID, L.DateCreated, L.ApplicantID, 
--                      L.CompanyID, L.GUID_Documents
--FROM         t_Loan AS L LEFT OUTER JOIN
--                      t_CarInfo AS CI ON L.ID = CI.LoanID LEFT OUTER JOIN
--                      t_Loan_Borrower AS L_B ON L.ID = L_B.LoanID
--WHERE     (L_B.LoanID IS NULL)
--
--REVERT
----COMMIT
--ROLLBACK

